﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CodeGenerator
{
	public class TableFieldInfo
	{
		public const string SQL = @"
select  C.column_id as ColumnId,
        C.name,
        case when IDX.PrimaryKey = '√' then 1
             else 0
        end as PrimaryKey,
        C.is_nullable as Nullable,
        isnull(PFD.[value], N'') as ColumnDesc
from    sys.columns C
inner join sys.objects O on C.[object_id] = O.[object_id]
                            and O.type = 'U'
                            and O.is_ms_shipped = 0
inner join sys.types T on C.user_type_id = T.user_type_id
left join sys.extended_properties PFD on PFD.class = 1
                                         and C.[object_id] = PFD.major_id
                                         and C.column_id = PFD.minor_id
left join -- 索引及主键信息
        ( select    IDXC.[object_id],
                    IDXC.column_id,
                    PrimaryKey = case when IDX.is_primary_key = 1 then N'√'
                                      else N''
                                 end
          from      sys.indexes IDX
          inner join sys.index_columns IDXC on IDX.[object_id] = IDXC.[object_id]
                                               and IDX.index_id = IDXC.index_id
          inner join -- 对于一个列包含多个索引的情况,只显示第个索引信息
                    ( select    [object_id],
                                Column_id,
                                index_id = min(index_id)
                      from      sys.index_columns
                      group by  [object_id],
                                Column_id
                    ) IDXCUQ on IDXC.[object_id] = IDXCUQ.[object_id]
                                and IDXC.Column_id = IDXCUQ.Column_id
                                and IDXC.index_id = IDXCUQ.index_id
        ) IDX on C.[object_id] = IDX.[object_id]
                 and C.column_id = IDX.column_id
where   O.name = '{0}'
order by C.column_id 
";
		public string TableName
		{
			get;
			set;
		}

		public int ColumnId
		{
			get;
			set;
		}

		public string Name
		{
			get;
			set;
		}

		public string AliasName
		{
			get;
			set;
		}

		public bool PrimaryKey
		{
			get;
			set;
		}

		public bool Nullable
		{
			get;
			set;
		}

		public string ColumnDesc
		{
			get;
			set;
		}
	}
}
